package com.example.easypoi.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.example.easypoi.entity.ExcelUser;
import com.example.easypoi.handler.UserVerifyHandler;
import com.example.easypoi.util.ExcelStyleUtil;
import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author yangj
 * @since
 */

@RestController
@RequestMapping("/user")
public class UserController {


    @Autowired
    private UserVerifyHandler userVerifyHandler;

    @PostMapping("/upload")
    public String upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {

        ImportParams params = new ImportParams();
        ExcelImportResult<ExcelUser> result;
        try {
            // 表头设置为1行
            params.setHeadRows(1);
            // 标题行设置为0行，默认是0，可以不设置
            params.setTitleRows(0);
            //开启检验
            params.setNeedVerfiy(true);
            params.setVerifyHandler(userVerifyHandler);
            result = ExcelImportUtil.importExcelMore
                    (multipartFile.getInputStream(), ExcelUser.class, params);
        } finally {
            // 清除threadLocal 防止内存泄漏
            ThreadLocal<List<ExcelUser>> threadLocal = userVerifyHandler.getThreadLocal();
            if (threadLocal != null) {
                threadLocal.remove();
            }
        }

        System.out.println(result.getFailList().toString());
        System.out.println(result.getList().toString());


        return null;

    }


    @GetMapping("/getList")
    public String getList(HttpServletRequest request, HttpServletResponse response) {
        List<ExcelUser> list = new ArrayList<>();
        ExcelUser excelUser = new ExcelUser();
        excelUser.setName("张三");
        excelUser.setAge("20");
        excelUser.setSex("男");
        ExcelUser excelUser1 = new ExcelUser();
        excelUser1.setName("李四");
        excelUser1.setAge("30");
        excelUser1.setSex("女");
        list.add(excelUser);
        list.add(excelUser1);
        ExportParams exportParams = new ExportParams("计算机一班学生", "学生");
        exportParams.setStyle(ExcelStyleUtil.class);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,
                ExcelUser.class, list);

        // 判断数据
        if (workbook == null) {
            return "fail";
        }
        // 设置excel的文件名称
        String excelName = "测试导出";
        // 重置响应对象
        response.reset();
        // 当前日期，用于导出文件名称


        // 指定下载的文件名--设置响应头
        response.setHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        // 写出数据输出流到页面
        try {
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "success";
    }

    /**
     * 模板导出方法
     *
     * @return
     */
    @GetMapping("templateDerive")
    public String templateDerive(String filepath, Integer sheetNo) throws IOException {
        StringBuilder sb = new StringBuilder();
        ArrayList<String> list1 = new ArrayList<>();
        filepath = "C:\\Users\\yangj\\Desktop\\附件10：数字压力计校准记录（拟废止）.xls";
        sheetNo = 0;
        Workbook workbook1 = getWorkbook(filepath);
        if (workbook1 != null) {
            if (sheetNo == null) {
                int numberOfSheets = workbook1.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    Sheet sheet = workbook1.getSheetAt(i);
                    if (sheet == null) {
                        continue;
                    }
                    sb.append(readExcelSheet(sheet));
                }
            } else {
                Sheet sheet = workbook1.getSheetAt(sheetNo);
                if (sheet != null) {
                    list1 = (ArrayList<String>) readExcelSheet(sheet);
//                    System.out.println(readExcelSheet(sheet));
                    System.out.println(list1.get(5));
                }
            }
        }
//        return list;
        //这里是模板的路径
        TemplateExportParams params = new TemplateExportParams(
                "excel/数字压力计校准记录导出模板.xlsx");
        Map<String, Object> map = new HashMap<>();
        List<ExcelUser> list = new ArrayList<>();
        //设置日期格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-hh-mm-ss");
        map.put("1001", list1.get(33));
        map.put("1002", list1.get(35));
        map.put("1003", list1.get(37));
        map.put("1004", list1.get(49));
        map.put("1005", list1.get(50));
        map.put("1006", list1.get(52));
        map.put("1007", list1.get(54));
        map.put("1008", list1.get(66));
        map.put("1009", list1.get(68));
        map.put("1010", list1.get(70));
        map.put("1011", list1.get(76));
        map.put("1012", list1.get(82));
        map.put("1013", list1.get(83));
        map.put("1014", list1.get(84));
        map.put("1015", list1.get(107));
        map.put("1016", list1.get(112));
        map.put("1017", list1.get(115));
        map.put("1018", list1.get(120));
        map.put("1019", list1.get(123));
        map.put("1020", list1.get(124));
        map.put("1021", list1.get(125));
        map.put("1022", list1.get(126));
        map.put("1023", list1.get(127));
        map.put("1029", list1.get(128));
        map.put("1024", list1.get(131));
        map.put("1025", list1.get(132));
        map.put("1026", list1.get(133));
        map.put("1027", list1.get(134));
        map.put("1028", list1.get(135));
        map.put("1030", list1.get(139));
        map.put("1031", list1.get(144));
        map.put("1032", list1.get(147));
        map.put("1033", list1.get(152));
        map.put("1034", list1.get(168));
        map.put("1035", list1.get(193));
        map.put("1036", list1.get(194));
        map.put("1037", list1.get(195));
        map.put("1038", list1.get(196));
        map.put("1039", list1.get(197));
        map.put("1040", list1.get(198));
        map.put("1041", list1.get(199));
        map.put("1042", list1.get(201));
        map.put("1043", list1.get(202));
        map.put("1044", list1.get(203));
        map.put("1045", list1.get(204));
        map.put("1046", list1.get(205));
        map.put("1047", list1.get(206));
        map.put("1048", list1.get(207));
        map.put("1049", list1.get(209));
        map.put("1050", list1.get(210));
        map.put("1051", list1.get(211));
        map.put("1052", list1.get(212));
        map.put("1053", list1.get(213));
        map.put("1054", list1.get(214));
        map.put("1055", list1.get(215));
        map.put("1056", list1.get(217));
        map.put("1057", list1.get(218));
        map.put("1058", list1.get(219));
        map.put("1059", list1.get(220));
        map.put("1060", list1.get(221));
        map.put("1061", list1.get(222));
        map.put("1062", list1.get(223));
        map.put("1063", list1.get(225));
        map.put("1064", list1.get(226));
        map.put("1065", list1.get(227));
        map.put("1066", list1.get(228));
        map.put("1067", list1.get(229));
        map.put("1068", list1.get(230));
        map.put("1069", list1.get(231));
        map.put("1070", list1.get(290));
        map.put("1071", list1.get(293));
        map.put("1072", list1.get(298));
        map.put("1073", list1.get(301));
        map.put("1074", list1.get(306));
        map.put("1075", list1.get(309));
        map.put("1076", list1.get(314));

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        //存储位置
        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/excel/" +list1.get(33) + "模板导出.xlsx");
        workbook.write(fos);
        fos.close();
        return "成功";

    }

    public static final String OFFICE_EXCEL_XLS = "xls";

    public static final String OFFICE_EXCEL_XLSX = "xlsx";
    /**
     * 读取指定Sheet也的内容
     * @param filepath filepath 文件全路径
     * @param sheetNo sheet序号,从0开始,如果读取全文sheetNo设置null
     */

    public static List<String> readExcel(String filepath, Integer sheetNo)
            throws EncryptedDocumentException, InvalidFormatException, IOException {
        StringBuilder sb = new StringBuilder();
        ArrayList<String> list = new ArrayList<>();
        filepath = "C:\\Users\\yangj\\Desktop\\附件10：数字压力计校准记录（拟废止）.xls";
        sheetNo = 0;
        Workbook workbook = getWorkbook(filepath);
        if (workbook != null) {
            if (sheetNo == null) {
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    if (sheet == null) {
                        continue;
                    }
                    sb.append(readExcelSheet(sheet));
                }
            } else {
                Sheet sheet = workbook.getSheetAt(sheetNo);
                if (sheet != null) {
                    list = (ArrayList<String>) readExcelSheet(sheet);
//                    System.out.println(readExcelSheet(sheet));
                }
            }
        }
        return list;
    }


    /**
     * 根据文件路径获取Workbook对象
     * @param filepath 文件全路径
     */
    public static Workbook getWorkbook(String filepath)
            throws EncryptedDocumentException, InvalidFormatException, IOException {
        InputStream is = null;
        Workbook wb = null;
        if (StringUtils.isBlank(filepath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = getSuffiex(filepath);
            if (StringUtils.isBlank(suffiex)) {
                throw new IllegalArgumentException("文件后缀不能为空");
            }
            if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {
                try {
                    is = new FileInputStream(filepath);
                    wb = WorkbookFactory.create(is);
                } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
                    throw new RuntimeException(e);
                } finally {
                    if (is != null) {
                        is.close();
                    }
                    if (wb != null) {
                        wb.close();
                    }
                }
            } else {
                throw new IllegalArgumentException("该文件非Excel文件");
            }
        }
        return wb;
    }

    /**
     * 获取后缀
     * @param filepath filepath 文件全路径
     */
    private static String getSuffiex(String filepath) {
        if (StringUtils.isBlank(filepath)) {
            return "";
        }
        int index = filepath.lastIndexOf(".");
        if (index == -1) {
            return "";
        }
        return filepath.substring(index + 1, filepath.length());
    }

    //返回的内容用#按行分隔，便于在后台解析字符串
    private static List<String> readExcelSheet(Sheet sheet) {
        StringBuilder sb = new StringBuilder();
        ArrayList<String> list = new ArrayList<>();
        if(sheet != null){
            int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数
            for (int i = 0; i <= rowNos; i++) {// 遍历行
                Row row = sheet.getRow(i);
                if(row != null){
                    int columNos = row.getLastCellNum();// 表头总共的列数
                    for (int j = 0; j < columNos; j++) {
                        Cell cell = row.getCell(j);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            if(j == (columNos-1)){
                                sb.append(cell.getStringCellValue());
                                list.add(cell.getStringCellValue());
                            }else{
                                sb.append(cell.getStringCellValue() + " ");
                                list.add(cell.getStringCellValue() + " ");
                            }
                        }
                    }
                }
                sb.append("#");
            }
        }
        return list;
    }

//    @ResponseBody
//    @PostMapping("readExcelData")
//    public String[][] readExcelData(File file, int ignoreRows) throws Exception{
//        List<String[]> result = new ArrayList<String[]>();
//        int rowSize = 0;
//        BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
//        //打开HSSWorkbook
//        POIFSFileSystem fs = new POIFSFileSystem(in);
//        HSSFWorkbook wb = new HSSFWorkbook(fs);
//        HSSFCell cell = null;
//        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
//            HSSFSheet st = wb.getSheetAt(sheetIndex);
//            //从规定位置，开始逐行读取内容
//            for (int rowIndex = ignoreRows; rowIndex < st.getLastRowNum(); rowIndex++) {
//                HSSFRow row = st.getRow(rowIndex);
//                if(row == null){
//                    continue;
//                }
//                int tempRowSize = row.getLastCellNum() + 1;
//                if(tempRowSize > rowSize){
//                    rowSize = tempRowSize;
//                }
//                String[] values = new String[rowSize];
//                Arrays.fill(values, "");
//                boolean hasValue = false;
//                for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
//                    String value = "";
//                    cell = row.getCell(columnIndex);
//                    if(cell != null){
//                        //设置字体编码
//                        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//                        switch (cell.getCellType()) {
//                            case HSSFCell.CELL_TYPE_STRING:
//                                value = cell.getStringCellValue();
//                                break;
//                            case HSSFCell.CELL_TYPE_NUMERIC:
//                                if(HSSFDateUtil.isCellDateFormatted(cell)){
//                                    Date date = cell.getDateCellValue();
//                                    if(date != null){
//                                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
//                                    }else{
//                                        value = "";
//                                    }
//                                }else{
//                                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
//                                }
//                                break;
//                            case HSSFCell.CELL_TYPE_FORMULA:
//                                //导入时如果为公式生成的数据则无值
//                                if(!cell.getStringCellValue().equals("")){
//                                    value = cell.getStringCellValue();
//                                }else{
//                                    value = cell.getNumericCellValue() + "";
//                                }
//                                break;
//                            case HSSFCell.CELL_TYPE_BLANK:
//                                break;
//                            case HSSFCell.CELL_TYPE_ERROR:
//                                value = "";
//                                break;
//                            case HSSFCell.CELL_TYPE_BOOLEAN:
//                                value = (cell.getBooleanCellValue() == true ? "Y":"N");
//                                break;
//                            default:
//                                value = "";
//                                break;
//                        }
//                    }
//                    if(columnIndex  == 0 && value.trim().equals("")){
//                        break;
//                    }
//                    values[columnIndex] = removeSpaceTrim(value);
//                    hasValue = true;
//                }
//                if(hasValue){
//                    result.add(values);
//                }
//            }
//        }
//        in.close();
//        String[][] returnArray = new String[result.size()][rowSize];
//        for (int i = 0; i < returnArray.length; i++) {
//            returnArray[i] = (String[]) result.get(i);
//        }
//        return returnArray;
//    }

    /**
     * 去掉字符串右边的空格
     * @param
     * @return 处理后的结果
     */
//    private static String removeSpaceTrim(String value) {
//        if(StringUtils.isEmpty(value)){
//            return "";
//        }
//        int length = value.length();
//        for (int i = length - 1; i >= 0; i--) {
//            if(value.charAt(i) != 0x20){
//                break;
//            }
//            length--;
//        }
//        return value.substring(0, length);
//    }

}